package com.jscloud.sparksql

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SparkSession

object SparkSqlTest {
        def main(args: Array[String]): Unit = {
                val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSQL")
                val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
                val sc: SparkContext = spark.sparkContext
                sc.setLogLevel("WARN")
                //1. 读取文件的数据test.txt
                val rdd1 = sc.textFile(this.getClass.getClassLoader.getResource("test.txt").getPath)
                /*
                val df = spark.read.options(
                        Map("inferSchema" -> "true", "delimiter" -> " ", "header" -> "true")).text(this.getClass.getClassLoader.getResource("test.txt").getPath)
                //   # 创建表用于sql查询
                df.createOrReplaceTempView("Student")
            */
                val header = rdd1.first()
                val rdd2 = rdd1.filter(_ != header).map(x => x.split(" "))

                //把rdd与样例类进行关联
                val studentRDD = rdd2.map(x => Student(x(0), x(1), x(2).toInt, x(3), x(4), x(5).toInt))
                //把rdd转换成DataFrame
                import spark.implicits._ // 隐式转换

                val studentDF = studentRDD.toDF

                //打印schema信息
                //studentDF.printSchema
                //展示数据
                // studentDF.show()
                //将df注册成为一张表
                studentDF.createTempView("Student")

                //2.   一共有多少个小于20岁的人参加考试？
                spark.sql("select count(*) from Student where age < 20").show()

                //3.   一共有多少个等于20岁的人参加考试？
                spark.sql("select count(*) from Student where age = 20").show()

                //4.   一共有多少个大于20岁的人参加考试？
                spark.sql("select count(*) from Student where age > 20").show()

                //5.   一共有多个男生参加考试？
                spark.sql("select count(*) as sex from Student where sex = '男'").show()

                //6.   一共有多少个女生参加考试？
                spark.sql("select count(*) as sex from Student where sex = '女'").show()

                //7.  12 班有多少人参加考试？
                spark.sql("select count(*) as students from Student where classid = '12'").show()

                // 8.  13 班有多少人参加考试？
                spark.sql("select count(*) as students from Student where classid = '13'").show()

                //9.   语文科目的平均成绩是多少？
                spark.sql("select Round(avg(result),2) as chinesescore from Student where subject = 'chinese'").show()

                // 10.   数学科目的平均成绩是多少？
                spark.sql("select cast(avg(result) as decimal(10,2)) as mathscore from Student where subject = 'math'")
                        .show()

                //11.   英语科目的平均成绩是多少？
                spark.sql("select Round(avg(result),2) as englishscore from Student where subject = 'english'").show()

                //12.   每个人平均成绩是多少？
                spark.sql("select name, Round(avg(result),2) as score from Student group by name").show()

                //13.  12 班平均成绩是多少？
                spark.sql("select classid, Round(avg(result),2) as score from Student where classid='12' group by " +
                        "classid").show()

                //14.  12 班男生平均总成绩是多少？
                spark.sql("select Round(sum(t.score),2) as score from (select classid, name, avg(result) as score " +
                        "from Student where sex='男' group by classid, name having classid='12') t ").show()

                //15.  12 班女生平均总成绩是多少？
                spark.sql("select Round(sum(t.score),2) as score from (select name, classid, avg(result) as score from " +
                        "Student where sex='女' group by classid, name having classid='12') t ").show()

                //16.  13 班平均成绩是多少？
                spark.sql("select classid, Round(avg(result),2) as score from Student group by classid having  " +
                        "classid='13' ").show()

                //17.  13 班男生平均总成绩是多少？
                spark.sql("select  Round(sum(t.score),2) as score from (select name, classid, avg(result) as score from " +
                        "Student where sex='男' group by classid, name having classid='13') t ").show()

                //18.  13 班女生平均总成绩是多少？
                spark.sql("select  Round(sum(t.score),2) as score from (select name, classid, avg(result) as score from " +
                        "Student where sex='女' group by classid, name having classid='13') t ").show()

                //19.   全校语文成绩最高分是多少？
                spark.sql("select max(result) as top1forchinese from Student where subject = 'chinese' ").show()

                //20.  12 班语文成绩最低分是多少？
                spark.sql("select min(result) as class12forchinese from Student where classid = '12' and subject = " +
                        "'chinese' ").show()

                //21.  13 班数学最高成绩是多少？
                spark.sql("select max(result) as top1forclass13 from Student where classid = '13' ").show()

                //22.   总成绩大于150分的12班的女生有几个？
                spark.sql("select count(1) from (select classid, name, sum(result) as score from Student group by " +
                        "classid,  name, sex having classid='12'  and sex = '女') t where t.score > 150").show()

                //23.   总成绩大于150分，且数学大于等于70，且年龄大于等于19岁的学生的平均成绩是多少？
                spark.sql("select s.score from" +
                        " (select  tmp.name, Round(avg( tmp.result),2) as score from (select s.* from Student s, (select name from Student  where  subject='math' " +
                        "and result >=70 and age >= 19) t  where s.name = t.name) tmp   group by  tmp.name) s,  " +
                        "(select name, sum(result) as totalscore from Student group by name ) t " +
                        "where  s.name = t.name and  t.totalscore > 150").show()

                spark.stop()
        }

}

//定义一个样例类
//班级  姓名  年龄  性别  科目       成绩
//12    宋江   25    男     chinese   50
case class Student(classid: String, name: String, age: Int, sex: String, subject: String, result: Int)